IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddCOIUserHierarchyMap]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddCOIUserHierarchyMap]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************            
* Name:   [AddCOIUserHierarchyMap]            
* Purpose:  Sets the hierarchy between the parent and child admpersonIds
			And grants the user access to COI if one does not exist. 
* NOte: The access will be granted only for users with fwkDomainUserId.            
* PARAMETERS(IN)            
* Name          Description                 
* -------------      -------------------------------------------            
* @xml       xml Document containing the hierarchy(REQUIRED)      
 The passed in XML has the following form:    
  <NewDataSet>    
   <Table>    
    <ParentId>234711</ParentId> 
	<ChildId>78543</ChildId>
	<Removed>0</Removed>  
    </Table>    
   <Table>    
    <ParentId>185678</ParentId> 
	<ChildId>67543</ChildId>
	<Removed>0</Removed>    
   </Table>    
   ...    
  </NewDataSet>  
               
*********************************************************************/            
            
CREATE PROCEDURE [dbo].[AddCOIUserHierarchyMap]            
(            
@xml TEXT       
)            
AS BEGIN            
	SET NOCOUNT ON            
	            
	DECLARE @xmlDoc INT
	DECLARE @admPersonIds TABLE (Sequence INT IDENTITY, [ParentId] INT,[ChildId] INT,[Removed] BIT, [HierarchyTypeId] INT)              
	EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xml     
	INSERT INTO @admPersonIds SELECT [parentId],[childId],[removed],[HierarchyTypeId] FROM OPENXML (@xmlDoc, '//Table1',2) WITH ([parentId] INT,[childId] INT,[removed] BIT,[HierarchyTypeId] INT)       
	EXEC sp_xml_removedocument @xmlDoc       
	 
	DECLARE @parentId INT
	DECLARE @childId INT
	DECLARE @HierarchyTypeId INT

	DECLARE hierarchyCursor CURSOR FOR    
	SELECT [parentId],[childId],[HierarchyTypeId] FROM @admPersonIds       
	   
	OPEN hierarchyCursor    
	-- Perform the first fetch.    
	FETCH NEXT FROM hierarchyCursor into @parentId,@ChildId,@HierarchyTypeId
  	-- Check @@FETCH_STATUS to see if there are any more rows to fetch.    
	WHILE @@FETCH_STATUS = 0    
	BEGIN    
		IF EXISTS(SELECT 1 FROM COI_Hierarchy_Map WHERE ParentId=@parentID AND ChildId=@ChildId AND HierarchyTypeId = @HierarchyTypeId)    
		BEGIN    
			UPDATE COI_Hierarchy_Map SET Removed=0,HierarchyTypeId = @HierarchyTypeId WHERE ParentId=@parentID AND ChildId=@childId AND HierarchyTypeId = @HierarchyTypeId
		END    
		ELSE   
		BEGIN    
			INSERT INTO COI_Hierarchy_Map VALUES (@parentId,@childId,0,@HierarchyTypeId)    
		END    
		
		DECLARE @fwkDomainUserId INT
		SET @fwkDomainUserId = NULL
		SELECT @fwkDomainUserId=FwkDomainUserID FROM AdmPerson WHERE Id=@childId AND AdmPersonTypeId=1   
		IF(@fwkDomainUserId IS NOT NULL)  
		BEGIN  
			DECLARE @moduleBitMask INT
			SET @moduleBitMask = NULL
			SELECT @moduleBitMask = ModuleBitMask FROM fwkApplicationuser WHERE fwkDomainUserID=@fwkDomainUserId  
			IF @moduleBitMask IS NULL
				INSERT INTO FwkApplicationUser(ApplicationBitmask,IsActiveBitmask,FwkDomainUserId,ModuleBitmask,ModuleRoleBitmask)    
				VALUES(4,4,@fwkDomainUserId,128,0)--Default values    
			ELSE IF(@moduleBitMask & 128 = 0)  
			BEGIN  
				SET @moduleBitMask=@moduleBitMask + 128  
				UPDATE fwkApplicationuser SET ModuleBitMask = @moduleBitMask WHERE fwkDomainUserID=@fwkDomainUserId  
			END  
		END   
		-- This is executed as long as the previous fetch succeeds.    
		FETCH NEXT FROM hierarchyCursor into @parentId,@ChildId,@HierarchyTypeId
	END    
 CLOSE hierarchyCursor    
 DEALLOCATE hierarchyCursor    
END    